"""optimize user system for dict config

Revision ID: d30d04b6e36b
Revises: 272db9f0a97e
Create Date: 2025-01-21 11:48:47.344037

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql

# revision identifiers, used by Alembic.
revision = 'd30d04b6e36b'
down_revision = '272db9f0a97e'
branch_labels = None
depends_on = None


def upgrade():
    # ### 第一阶段：用户体系表结构优化 ###
    
    # 1. 优化users表结构 - 添加新字段
    # 检查并添加wechat_number字段
    connection = op.get_bind()
    result = connection.execute(sa.text("SHOW COLUMNS FROM users LIKE 'wechat_number'"))
    if not result.fetchone():
        with op.batch_alter_table('users') as batch_op:
            batch_op.add_column(sa.Column('wechat_number', sa.String(50), comment='微信号'))
    
    # 更新real_name字段 - 先处理NULL值，再设置NOT NULL
    connection = op.get_bind()
    # 将NULL值更新为空字符串
    connection.execute(sa.text("UPDATE users SET real_name = '' WHERE real_name IS NULL"))
    
    with op.batch_alter_table('users') as batch_op:
        batch_op.alter_column('real_name', 
                            existing_type=sa.String(50), 
                            nullable=False, 
                            comment='真实姓名（不可修改）')
        # 注意：hometown和work_city在user_profiles表中已存在
    
    # 2. 优化user_profiles表结构 - 修改字段类型和添加新字段
    with op.batch_alter_table('user_profiles') as batch_op:
        # 将现有字段改为字典值存储，指定原始类型
        batch_op.alter_column('education', 
                            existing_type=sa.String(50), 
                            type_=sa.String(50), 
                            comment='学历（字典值）')
        batch_op.alter_column('occupation', 
                            existing_type=sa.String(100), 
                            type_=sa.String(50), 
                            comment='职业（字典值）')
        batch_op.alter_column('income_range', 
                            existing_type=sa.String(50), 
                            type_=sa.String(50), 
                            comment='收入范围（字典值）')
        batch_op.alter_column('housing', 
                            existing_type=sa.String(50), 
                            type_=sa.String(50), 
                            comment='购房情况（字典值）')
        batch_op.alter_column('car', 
                            existing_type=sa.String(50), 
                            type_=sa.String(50), 
                            comment='购车情况（字典值）')
        
        # 将吸烟饮酒字段从整型改为字典值
        batch_op.alter_column('smoking', 
                            existing_type=sa.Integer, 
                            new_column_name='smoking_habit', 
                            type_=sa.String(50), 
                            comment='吸烟习惯（字典值）')
        batch_op.alter_column('drinking', 
                            existing_type=sa.Integer, 
                            new_column_name='drinking_habit', 
                            type_=sa.String(50), 
                            comment='饮酒习惯（字典值）')
        
        # 添加新字段
        batch_op.add_column(sa.Column('marital_status', sa.String(50), comment='婚姻状况（字典值）'))
        batch_op.add_column(sa.Column('personality_traits', sa.Text, comment='性格特征标签（JSON数组）'))
        batch_op.add_column(sa.Column('hobby_tags', sa.Text, comment='兴趣爱好标签（JSON数组）'))
    
    # 3. 创建用户视频表
    op.create_table('user_videos',
        sa.Column('id', sa.Integer, primary_key=True, comment='主键ID'),
        sa.Column('user_id', sa.Integer, nullable=False, comment='用户ID'),
        sa.Column('video_file_id', sa.Integer, nullable=False, comment='视频文件ID（关联file_uploads）'),
        sa.Column('video_url', sa.String(255), nullable=False, comment='视频URL'),
        sa.Column('thumbnail_url', sa.String(255), comment='缩略图URL'),
        sa.Column('duration', sa.Integer, comment='视频时长（秒）'),
        sa.Column('sort_order', sa.Integer, default=0, comment='排序'),
        sa.Column('status', sa.Integer, default=1, comment='状态：1待审核2通过3拒绝'),
        sa.Column('audit_reason', sa.String(200), comment='审核原因'),
        sa.Column('created_at', sa.DateTime, default=sa.func.current_timestamp(), comment='创建时间'),
        sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['video_file_id'], ['file_uploads.id'], ondelete='CASCADE'),
    )
    
    # 添加索引
    op.create_index('idx_user_videos_user_status', 'user_videos', ['user_id', 'status'])
    
    # 4. 优化mate_criteria表结构
    with op.batch_alter_table('mate_criteria') as batch_op:
        # 将学历要求改为JSON数组（最多3项）
        batch_op.alter_column('education', 
                            existing_type=sa.String(100), 
                            type_=sa.Text, 
                            comment='学历要求（JSON数组，最多3项）')
        # 收入要求改为字典值
        batch_op.alter_column('income_range', 
                            existing_type=sa.String(100), 
                            type_=sa.String(50), 
                            comment='收入要求（字典值）')
        # 职业要求改为JSON数组（最多3项）
        batch_op.alter_column('occupation', 
                            existing_type=sa.String(200), 
                            type_=sa.Text, 
                            comment='职业要求（JSON数组，最多3项）')
        
        # 删除现有的marital_status字段，添加新的marital_requirement字段
        batch_op.drop_column('marital_status')
        batch_op.add_column(sa.Column('marital_requirement', sa.Text, comment='婚姻状态要求（JSON数组，最多3项）'))
        
        # 添加新的择偶要求字段
        batch_op.add_column(sa.Column('smoking_requirement', sa.String(50), comment='吸烟要求（字典值）'))
        batch_op.add_column(sa.Column('drinking_requirement', sa.String(50), comment='饮酒要求（字典值）'))
        batch_op.add_column(sa.Column('additional_requirements', sa.Text, comment='补充要求（文本）'))
        
        # 重命名housing_requirement字段（如果不存在则添加）
        batch_op.alter_column('housing_requirement', 
                            existing_type=sa.String(100), 
                            type_=sa.String(50), 
                            comment='住房要求（字典值）')
    
    # 5. 优化user_verifications表结构
    with op.batch_alter_table('user_verifications') as batch_op:
        batch_op.add_column(sa.Column('verification_data', sa.JSON, comment='认证详细数据'))
        batch_op.add_column(sa.Column('expires_at', sa.DateTime, comment='认证过期时间'))
        batch_op.add_column(sa.Column('certificate_number', sa.String(100), comment='证件号码（加密）'))


def downgrade():
    # ### 第一阶段回滚操作 ###
    
    # 回滚user_verifications表
    with op.batch_alter_table('user_verifications') as batch_op:
        batch_op.drop_column('certificate_number')
        batch_op.drop_column('expires_at')
        batch_op.drop_column('verification_data')
    
    # 回滚mate_criteria表
    with op.batch_alter_table('mate_criteria') as batch_op:
        batch_op.drop_column('additional_requirements')
        batch_op.drop_column('drinking_requirement')
        batch_op.drop_column('smoking_requirement')
        batch_op.drop_column('marital_requirement')
        batch_op.add_column(sa.Column('marital_status', sa.String(50), comment='婚姻状况'))
        
        # 恢复原始字段类型
        batch_op.alter_column('occupation', type_=sa.String(200), comment='职业偏好')
        batch_op.alter_column('income_range', type_=sa.String(100), comment='收入要求')
        batch_op.alter_column('education', type_=sa.String(100), comment='学历要求')
    
    # 删除用户视频表
    op.drop_index('idx_user_videos_user_status', 'user_videos')
    op.drop_table('user_videos')
    
    # 回滚user_profiles表
    with op.batch_alter_table('user_profiles') as batch_op:
        batch_op.drop_column('hobby_tags')
        batch_op.drop_column('personality_traits')
        batch_op.drop_column('marital_status')
        
        # 恢复原始字段类型
        batch_op.alter_column('drinking_habit', new_column_name='drinking', type_=sa.Integer, comment='是否饮酒：0不饮酒1偶尔2经常')
        batch_op.alter_column('smoking_habit', new_column_name='smoking', type_=sa.Integer, comment='是否吸烟：0不吸烟1偶尔2经常')
        batch_op.alter_column('car', type_=sa.String(50), comment='购车情况')
        batch_op.alter_column('housing', type_=sa.String(50), comment='住房情况')
        batch_op.alter_column('income_range', type_=sa.String(50), comment='收入范围')
        batch_op.alter_column('occupation', type_=sa.String(100), comment='职业')
        batch_op.alter_column('education', type_=sa.String(50), comment='学历')
    
    # 回滚users表
    with op.batch_alter_table('users') as batch_op:
        batch_op.alter_column('real_name', nullable=True, comment='真实姓名')
        batch_op.drop_column('wechat_number')
